<?php
namespace Poi\EttsBundle\Model;

/**
 * @author Ricardo J. Vazquez
 * 
 * Collection of Tickets models
 */
class Tickets extends Collection
{
	public function init(){	
	}
	
	/**
	* Loads the collection with Ticket models
	*
	* @param $col 
	*/
	public function loadTickets(Collection $col){
		$sql = "SELECT
					VT.TicketNumber,
					VT.BadgeNumber AS 'Officer',
					VT.VIN AS 'Owner',
					'vehicle' AS 'Type',
					TT.InfractionKind,
					TT.Fine,
					TT.Point,
					TT.Article_Number,
					VT.Longitude,
					VT.Latitude,
					VT.Time
				FROM VehicleTicket VT
					INNER JOIN TicketType TT ON VT.TicketType=TT.TicketType
				UNION SELECT 
					DT.TicketNumber,
					DT.BadgeNumber,
					DT.LicenseNumber AS 'Owner',	
					'driver' AS 'Type',
					TT.InfractionKind,
					TT.Fine,
					TT.Point,
					TT.Article_Number,
					DT.Longitude,
					DT.Latitude,
					DT.Time
				FROM DriverTicket DT
					INNER JOIN TicketType TT ON DT.TicketType=TT.TicketType";
				
		$loadStmt = $this->conn->prepare($sql);

		$loadStmt->execute();

		$tickets = $loadStmt->getWrappedStatement()->fetchAll(
			\PDO::FETCH_CLASS, 
			"Poi\\EttsBundle\\Model\\Ticket", 
			array($this->conn)
		);
		$count = 0;
		foreach($tickets as $ticket){
			$ticket->setLoaded(true);
			$this->addItem($ticket, $count++);
		}
	}
	
	/**
	* Loads the collection with Ticket models pertaining only to vehicles
	*
	* @param $col 
	*/
	public function loadTicketsVehicle(Collection $col){
		if(!empty($this->id)){		
			$sql = "SELECT
						VT.TicketNumber,
						VT.BadgeNumber AS 'Officer',
						VT.VIN AS 'Owner',
						'vehicle' AS 'Type',
						TT.InfractionKind,
						TT.Fine,
						TT.Point,
						TT.Article_Number,
						VT.Longitude,
						VT.Latitude,
						VT.Time
					FROM VehicleTicket VT
						INNER JOIN TicketType TT ON VT.TicketType=TT.TicketType
					WHERE VT.VIN = :key";
				
			$loadStmt = $this->conn->prepare($sql);
			
			$loadStmt->bindValue("key", $this->id);

			$loadStmt->execute();

			$tickets = $loadStmt->getWrappedStatement()->fetchAll(
				\PDO::FETCH_CLASS, 
				"Poi\\EttsBundle\\Model\\Ticket", 
				array($this->conn)
			);

			$count = 0;
			foreach($tickets as $ticket){
				$ticket->setLoaded(true);
				$this->addItem($ticket, $count++);
			}
		}
	}
	
	/**
	* Loads the collection with Ticket models pertaining only to drivers
	*
	* @param $col 
	*/
	public function loadTicketsDriver(Collection $col){
		if(!empty($this->id)){		
			$sql = "SELECT 
						DT.TicketNumber,
						DT.BadgeNumber AS 'Officer',
						DT.LicenseNumber AS 'Owner',	
						'driver' AS 'Type',
						TT.InfractionKind,
						TT.Fine,
						TT.Point,
						TT.Article_Number,
						DT.Longitude,
						DT.Latitude,
						DT.Time
					FROM DriverTicket DT
						INNER JOIN TicketType TT ON DT.TicketType=TT.TicketType
					WHERE DT.LicenseNumber = :key";
				
			$loadStmt = $this->conn->prepare($sql);
			
			$loadStmt->bindValue("key", $this->id);

			$loadStmt->execute();

			$tickets = $loadStmt->getWrappedStatement()->fetchAll(
				\PDO::FETCH_CLASS, 
				"Poi\\EttsBundle\\Model\\Ticket", 
				array($this->conn)
			);

			$count = 0;
			foreach($tickets as $ticket){
				$ticket->setLoaded(true);
				$this->addItem($ticket, $count++);
			}
		}
	}
		
	/**
	* Loads the collection with Ticket models pertaining only to officers
	*
	* @param $col 
	*/
	public function loadTicketsOfficer(Collection $col){
		if(!empty($this->id)){		
			$sql = "SELECT
						VT.TicketNumber,
						VT.BadgeNumber AS 'Offier',
						VT.VIN AS 'Owner',
						'vehicle' AS 'Type',
						TT.InfractionKind,
						TT.Fine,
						TT.Point,
						TT.Article_Number,
						VT.Longitude,
						VT.Latitude,
						VT.Time
					FROM VehicleTicket VT
						INNER JOIN TicketType TT ON VT.TicketType=TT.TicketType
					WHERE VT.BadgeNumber = :key
					UNION SELECT 
						DT.TicketNumber,
						DT.BadgeNumber AS 'Officer',
						DT.LicenseNumber AS 'Owner',	
						'driver' AS 'Type',
						TT.InfractionKind,
						TT.Fine,
						TT.Point,
						TT.Article_Number,
						DT.Longitude,
						DT.Latitude,
						DT.Time
					FROM DriverTicket DT
						INNER JOIN TicketType TT ON DT.TicketType=TT.TicketType
					WHERE DT.BadgeNumber = :key";
				
			$loadStmt = $this->conn->prepare($sql);
			
			$loadStmt->bindValue("key", $this->id);

			$loadStmt->execute();

			$tickets = $loadStmt->getWrappedStatement()->fetchAll(
				\PDO::FETCH_CLASS, 
				"Poi\\EttsBundle\\Model\\Ticket", 
				array($this->conn)
			);

			$count = 0;
			foreach($tickets as $ticket){
				$ticket->setLoaded(true);
				$this->addItem($ticket, $count++);
			}
		}
	}
	
	public function toXML(\SimpleXMLElement &$xml=null){
		$return = false;
		if(is_null($xml)){
			$xml = new \SimpleXMLElement('<?xml version="1.0" encoding="utf-8" ?><xmlresponse></xmlresponse>');
			$return = true;
		}
		
		$length = $this->length();
		
		$ticketsXML = $xml->addChild('tickets');
		
		for($i = 0; $i < $length; $i++)
		{
			$this->getItem($i)->toXML($ticketsXML);
		}
				
		if($return){		
			return $xml->asXML();
		}
	}
}